﻿# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2Json
from com.zzh.utils.pagingUtils import MyPaging
from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
from com.zzh.utils.requestFieldMapDBField import fieldMap_Chen



class CategoryDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def insertCategory(self, info):
        """
        添加category信息
        :param info:
        :return:
        """
        catName = info['catName']
        catPcode = info['catPcode']
        catCode = info['catCode']
        catStatus = info['catStatus']
        catSort = info['catSort']

        sql = "insert into zzh_category (cat_name, cat_pcode, cat_code, cat_status, cat_sort) VALUES " \
              "(%s,%s,%s,%s,%s)"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [catName, catPcode, catCode, catStatus, catSort])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def findCategoryById(self, catId):
        """
        根据ID查询category信息
        :param catId:
        :return:
        """
        sql = "select * from zzh_category WHERE cat_id=%s"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [catId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.category_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def findCategoryByCondition(self, **kwargs):
        """
        category表的条件查询
        :param kwargs:
        :return:
        """

        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.categoryFieldMap
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM zzh_category WHERE " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM zzh_category WHERE " + whereSentence
        rs = cur.execute(sql)

        jsons = None
        if rs:
            rsSet = cur.fetchall()
            list0 = MyPaging().getPage(rs, pageIndex, pageSize, rsSet)
            jsons = Table2Json.category_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons,rs

    def findCategoryByCondition2(self, **kwargs):
        """
        category表的条件查询
        :param kwargs:
        :return:
        """

        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewCategoryItemsFieldMap
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM view_category_items WHERE " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM view_category_items WHERE " + whereSentence
        rs = cur.execute(sql)

        jsons = None
        if rs:
            rsSet = cur.fetchall()
            list0 = MyPaging().getPage(rs, pageIndex, pageSize, rsSet)
            jsons = Table2Json.view_category_items_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons, rs




    def deleteCatgory(self, idArray):
        """
        根据ID删除信息，可以传递一个或者多个ID，ID以数组形式传递
        :param idArray:
        :return:
        """
        sql = "delete from zzh_category WHERE cat_id=%s"
        cur = self.conn.cursor()
        for idd in idArray:
            rs = cur.execute(sql, [idd])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def updateCategory(self, info):
        """
        更新category
        :param info:
        :return:
        """
        catId = info['catId']
        catName = info['catName']
        catPcode = info['catPcode']
        catCode = info['catCode']
        catStatus = info['catStatus']
        catSort = info['catSort']

        sql = "update zzh_category set cat_name=%s,cat_pcode=%s,cat_code=%s,cat_status=%s,cat_sort=%s WHERE cat_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [catName, catPcode, catCode, catStatus, catSort, catId])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def selectCat(self,catId):
        """
        根据ID查询，返回元组
        :param catId:
        :return: data tuple of cat
        """
        sql = "select * from zzh_category WHERE cat_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [catId])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr
